﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;

using Bpms.Data;
using Bpms.Entity;
using Bpms.Config;

namespace Bpms.Data.SqlServer
{
    public partial class DataProvider
    {
        #region Private
        //当前文件对应的逻辑类名，为了写日志
        string businessTypeClassName = "Bpms.Data.SqlServer";
        //当前操作的数据表名
        string businessTypeTableName = string.Format("[{0}business_type]", ConfigContext.GetTablePrefix);

        /// <summary>
        /// 绑定数据到类别实体
        /// </summary>
        /// <param name="idr">idatareader</param>
        /// <returns></returns>
        IList<BusinessTypeInfo> GetDataBindedBusinessType(IDataReader idr)
        {
            IList<BusinessTypeInfo> businessTypeList = new List<BusinessTypeInfo>();

            while (idr.Read())
            {
                businessTypeList.Add(new BusinessTypeInfo() 
                {
                    ID = (int)idr["business_type_id"],
                    CHName = idr["ch_name"].ToString(),
                    ENName = idr["en_name"].ToString(),
                    Description = idr["description"].ToString(),
                    CategoryID = (int)idr["business_category_id"],
                    UnitPrice = Convert.ToDecimal((idr["unit_price"])),
                    CurrencyName = idr["currency_name"].ToString(),
                    CurrencySign = idr["currency_sign"].ToString(),
                    CHUnit = idr["ch_unit"].ToString(),
                    ENUnit = idr["en_unit"].ToString(),
                    CustomerID = (int)idr["customer_id"],
                    CHOrignUnit = idr["ch_origin_unit"].ToString(),
                    ENOrignUnit = idr["en_origin_unit"].ToString(),
                    CHTargetUnit = idr["ch_target_unit"].ToString(),
                    ENTargetUnit = idr["en_target_unit"].ToString(),
                    Type = Convert.ToByte(idr["type"]),
                    Remark = idr["remark"].ToString(),
                    SortNumber = (byte)idr["sort_number"],
                    IsDelete = Convert.ToBoolean(idr["is_delete"])
                });
            }

            return businessTypeList;
        }

        /// <summary>
        /// 绑定数据到业务类型扩展实体
        /// </summary>
        /// <param name="idr"></param>
        /// <returns></returns>
        IList<BusinessTypeExtensionInfo> GetDataBindedBusinessTypeExtension(IDataReader idr)
        {
            IList<BusinessTypeExtensionInfo> businessTypeExtensionList = new List<BusinessTypeExtensionInfo>();

            while (idr.Read())
            {
                businessTypeExtensionList.Add(new BusinessTypeExtensionInfo()
                {
                    ID = (int)idr["business_type_extension_id"],
                    BusinessTypeID = (int)idr["business_type_id"],
                    BeginRate = idr["beginning"].ToString(),
                    EndRate = idr["end"].ToString(),
                    UnitPrice = Convert.ToDecimal(idr["unit_price"]),
                    Type = Convert.ToByte(idr["type"]),
                    SortNumber = (byte)idr["sort_number"]
                });

            }

            return businessTypeExtensionList;
        }

        IList<MatchRateInfo> GetBindedMatchRate(IDataReader idr)
        {
            IList<MatchRateInfo> matchRateList = new List<MatchRateInfo>();

            while (idr.Read())
            {
                matchRateList.Add(new MatchRateInfo()
                {
                    ID = (int)idr["match_rate_id"],
                    Key = idr["key"].ToString(),
                    Value = idr["value"].ToString(),
                    Type = Convert.ToByte(idr["type"])
                });
            }

            return matchRateList;
        }

        /// <summary>
        /// 根据sql语句返回业务类型信息
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        BusinessTypeInfo GetBusinessTypeBySQL(string sql)
        {
            var list = GetBusinessTypeListBySQL(sql);

            if (list.Count > 0)
            {
                return list.First();
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 根据sql语句返回业务类型列表
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        IList<BusinessTypeInfo> GetBusinessTypeListBySQL(string sql)
        {
            IList<BusinessTypeInfo> list = new List<BusinessTypeInfo>();

            try
            {
                using (var idr = DbHelper.ExecuteReader(CommandType.Text, sql) as IDataReader)
                {
                    try
                    {
                        list = GetDataBindedBusinessType(idr);
                    }
                    catch (Exception e)
                    {
                        log.WriteLog(this.businessTypeClassName + ":获取绑定的业务类型信息时出现异常", e);
                    }
                    finally
                    {
                        if (idr != null)
                        {
                            idr.Close();
                        }
                    }
                }
            }
            catch (Exception e)
            {
                log.WriteLog(this.businessTypeClassName + ":检索业务类型表时数据库出现异常", e);
            }

            return list;
        }

        /// <summary>
        /// 根据sql语句返回业务类型扩展信息
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        BusinessTypeExtensionInfo GetBusinessTypeExtensionBySQL(string sql, params DbParameter[] parms)
        {
            var list = GetBusinessTypeExtensionListBySQL(sql, parms);

            if (list.Count > 0)
            {
                return list.First();
            }
            
            return null;
        }

        /// <summary>
        /// 根据sql语句返回业务类型扩展列表
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        IList<BusinessTypeExtensionInfo> GetBusinessTypeExtensionListBySQL(string sql, params DbParameter[] parms)
        {
            IList<BusinessTypeExtensionInfo> list = new List<BusinessTypeExtensionInfo>();

            try
            {
                using (var idr = DbHelper.ExecuteReader(CommandType.Text, sql, parms) as IDataReader)
                {
                    try
                    {
                        list = GetDataBindedBusinessTypeExtension(idr);
                    }
                    catch (Exception e)
                    {
                        log.WriteLog(this.businessTypeClassName + ":获取绑定的业务类型扩展信息时出现异常", e);
                    }
                    finally
                    {
                        if (idr != null)
                        {
                            idr.Close();
                        }
                    }
                }
            }
            catch(Exception e)
            {
                log.WriteLog(this.businessTypeClassName + "：检索业务类型扩展信息时数据库出现异常", e);
            }

            return list;
        }

        /// <summary>
        /// 根据sql语句返回匹配率信息
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        MatchRateInfo GetMatchRateBySQL(string sql, params DbParameter[] parms)
        {
            var list = GetMatchRateListBySQL(sql, parms);
            
            if (list.Count > 0)
            {
                return list.First();
            }
            
            return null;
        }

        /// <summary>
        /// 根据sql语句返回匹配率列表
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parms">sql参数</param>
        /// <returns></returns>
        IList<MatchRateInfo> GetMatchRateListBySQL(string sql, params DbParameter[] parms)
        {
            IList<MatchRateInfo> list = new List<MatchRateInfo>();

            try
            {
                using (var idr = DbHelper.ExecuteReader(CommandType.Text, sql, parms))
                {
                    try
                    {
                        list = GetBindedMatchRate(idr);
                    }
                    catch (Exception e)
                    {
                        log.WriteLog(this.businessTypeClassName + ":获取绑定的匹配率时出现异常", e);
                    }
                    finally
                    {
                        if (idr != null)
                        {
                            idr.Close();
                        }
                    }
                }
            }
            catch(Exception e)
            {
                log.WriteLog(this.businessTypeClassName + ":检索匹配率表时数据库发生异常", e);
            }

            return list;
        }

        /// <summary>
        /// 根据ID返回当前类别下的最大排序号
        /// </summary>
        /// <param name="businessCategoryId">当前类别ID</param>
        /// <returns></returns>
        byte GetBusinessTypeMaxSortNumber()
        {
            var sql = string.Format("SELECT MAX([sort_number]) FROM {0} ", this.businessTypeTableName);

            try
            {
                var result = (byte)DbHelper.ExecuteScalar(CommandType.Text, sql);
                return result;
            }
            catch (Exception e)
            {
                log.WriteLog(this.businessTypeClassName + "：获取业务类型最大排序号时发生异常", e);
                throw;
            }
        }

        /// <summary>
        /// 添加业务类型扩展信息
        /// </summary>
        /// <param name="businessTypeExtension">业务类型扩展</param>
        /// <param name="tran">事务</param>
        /// <returns></returns>
        bool InsertBusinessTypeExtension(BusinessTypeExtensionInfo businessTypeExtension, SqlTransaction tran)
        {
            DbParameter[] parms = 
            { 
                DbHelper.MakeInParam("@business_type_id", (DbType)SqlDbType.Int, 4, businessTypeExtension.BusinessTypeID), 
                DbHelper.MakeInParam("@beginning", (DbType)SqlDbType.VarChar, 10, businessTypeExtension.BeginRate), 
                DbHelper.MakeInParam("@end", (DbType)SqlDbType.VarChar, 10, businessTypeExtension.EndRate), 
                DbHelper.MakeInParam("@unit_price", (DbType)SqlDbType.Decimal, 9, businessTypeExtension.UnitPrice), 
                DbHelper.MakeInParam("@type", (DbType)SqlDbType.TinyInt, 1, businessTypeExtension.Type), 
                DbHelper.MakeInParam("@sort_number", (DbType)SqlDbType.TinyInt, 1, businessTypeExtension.SortNumber) 
            };

            try
            {
                if (DbHelper.ExecuteNonQuery(tran, CommandType.StoredProcedure, "bpms_add_business_type_extention", parms) > 0)
                {
                    return true;
                }
            }
            catch (Exception e)
            {
                log.WriteLog(this.businessTypeClassName, e);
                throw;
            }

            return false;
        }

        /// <summary>
        /// 批量删除当前业务类型下的扩展信息
        /// </summary>
        /// <param name="businessTypeId">业务类型ID</param>
        /// <param name="tran">事务</param>
        /// <returns></returns>
        bool DeleteAllExtensionsByBusinessTypeId(int businessTypeId, SqlTransaction tran)
        {
            var sql = string.Format("DELETE FROM {0}business_type_extension WHERE [business_type_id] = @business_type_id", ConfigContext.GetTablePrefix);
            DbParameter[] parms = 
            { 
                DbHelper.MakeInParam("@business_type_id", (DbType)SqlDbType.Int, 4, businessTypeId) 
            };

            try
            {
                if (DbHelper.ExecuteNonQuery(tran, CommandType.Text, sql, parms) > 0)
                {
                    return true;
                }
            }
            catch (Exception e)
            {
                log.WriteLog(this.businessTypeClassName, e);
                throw;
            }

            return false;
        }
        #endregion

        public bool InsertBusinessType(BusinessTypeInfo businessType, BusinessTypeExtensionInfo[] businessTypeExtensions)
        {
            try
            {
                businessType.SortNumber = GetBusinessTypeMaxSortNumber();
            }
            catch
            {
                throw;
            }
            DbParameter[] parms = 
            { 
                DbHelper.MakeInParam("@ch_name", (DbType)SqlDbType.NVarChar, 100, businessType.CHName), 
                DbHelper.MakeInParam("@en_name", (DbType)SqlDbType.VarChar, 100, businessType.ENName), 
                DbHelper.MakeInParam("@description", (DbType)SqlDbType.NVarChar, 300, businessType.Description), 
                DbHelper.MakeInParam("@business_category_id", (DbType)SqlDbType.Int, 4, businessType.CategoryID), 
                DbHelper.MakeInParam("@unit_price", (DbType)SqlDbType.Decimal, 9, businessType.UnitPrice), 
                DbHelper.MakeInParam("@currency_name", (DbType)SqlDbType.VarChar, 20, businessType.CurrencyName), 
                DbHelper.MakeInParam("@currency_sign", (DbType)SqlDbType.VarChar, 10, businessType.CurrencySign), 
                DbHelper.MakeInParam("@ch_unit", (DbType)SqlDbType.NVarChar, 50, businessType.CHUnit), 
                DbHelper.MakeInParam("@en_unit", (DbType)SqlDbType.VarChar, 50, businessType.ENUnit), 
                DbHelper.MakeInParam("@customer_id", (DbType)SqlDbType.Int, 4, businessType.CustomerID), 
                DbHelper.MakeInParam("@ch_origin_unit", (DbType)SqlDbType.NVarChar, 20, businessType.CHOrignUnit), 
                DbHelper.MakeInParam("@en_origin_unit", (DbType)SqlDbType.VarChar, 20, businessType.ENOrignUnit), 
                DbHelper.MakeInParam("@ch_target_unit", (DbType)SqlDbType.NVarChar, 20, businessType.CHTargetUnit), 
                DbHelper.MakeInParam("@en_target_unit", (DbType)SqlDbType.VarChar, 20, businessType.ENTargetUnit), 
                DbHelper.MakeInParam("@type", (DbType)SqlDbType.TinyInt, 1, businessType.Type), 
                DbHelper.MakeInParam("@remark", (DbType)SqlDbType.NVarChar, 200, businessType.Remark), 
                DbHelper.MakeInParam("@sort_number", (DbType)SqlDbType.TinyInt, 1, businessType.SortNumber), 
                DbHelper.MakeInParam("@is_delete", (DbType)SqlDbType.TinyInt, 1, 0) 
            };
            var connection = new SqlConnection(ConfigContext.GetDBConnectString);
            SqlTransaction tran = connection.BeginTransaction();

            try
            {
                var bussinessTypeId = (int)DbHelper.ExecuteScalar(tran, CommandType.StoredProcedure, "bpms_add_business_type", parms);

                foreach (var extension in businessTypeExtensions)
                {
                    extension.BusinessTypeID = bussinessTypeId;
                    InsertBusinessTypeExtension(extension, tran);
                }

                tran.Commit();
                return true;
            }
            catch (Exception e)
            {
                log.WriteLog(this.categoryClassName, e);
                try
                {
                    tran.Rollback();
                }
                catch (Exception ee)
                {
                    log.WriteLog(this.categoryClassName + "添加业务类型失败后事务回滚失败！", ee);
                }
            }
            finally
            {
                tran.Dispose();
                connection.Close();
            }

            return false;
        }

        public bool UpdateBusinessType(BusinessTypeInfo businessType, BusinessTypeExtensionInfo[] businessTypeExtensions)
        {
            DbParameter[] parms = 
            { 
                DbHelper.MakeInParam("@@business_type_id", (DbType)SqlDbType.Int, 4, businessType.ID),
                DbHelper.MakeInParam("@ch_name", (DbType)SqlDbType.NVarChar, 100, businessType.CHName), 
                DbHelper.MakeInParam("@en_name", (DbType)SqlDbType.VarChar, 100, businessType.ENName), 
                DbHelper.MakeInParam("@description", (DbType)SqlDbType.NVarChar, 300, businessType.Description), 
                DbHelper.MakeInParam("@business_category_id", (DbType)SqlDbType.Int, 4, businessType.CategoryID), 
                DbHelper.MakeInParam("@unit_price", (DbType)SqlDbType.Decimal, 9, businessType.UnitPrice), 
                DbHelper.MakeInParam("@currency_name", (DbType)SqlDbType.VarChar, 20, businessType.CurrencyName), 
                DbHelper.MakeInParam("@currency_sign", (DbType)SqlDbType.VarChar, 10, businessType.CurrencySign), 
                DbHelper.MakeInParam("@ch_unit", (DbType)SqlDbType.NVarChar, 50, businessType.CHUnit), 
                DbHelper.MakeInParam("@en_unit", (DbType)SqlDbType.VarChar, 50, businessType.ENUnit), 
                DbHelper.MakeInParam("@customer_id", (DbType)SqlDbType.Int, 4, businessType.CustomerID), 
                DbHelper.MakeInParam("@ch_origin_unit", (DbType)SqlDbType.NVarChar, 20, businessType.CHOrignUnit), 
                DbHelper.MakeInParam("@en_origin_unit", (DbType)SqlDbType.VarChar, 20, businessType.ENOrignUnit), 
                DbHelper.MakeInParam("@ch_target_unit", (DbType)SqlDbType.NVarChar, 20, businessType.CHTargetUnit), 
                DbHelper.MakeInParam("@en_target_unit", (DbType)SqlDbType.VarChar, 20, businessType.ENTargetUnit), 
                DbHelper.MakeInParam("@type", (DbType)SqlDbType.TinyInt, 1, businessType.Type), 
                DbHelper.MakeInParam("@remark", (DbType)SqlDbType.NVarChar, 200, businessType.Remark) 
            };
            var connection = new SqlConnection(ConfigContext.GetDBConnectString);
            SqlTransaction tran = connection.BeginTransaction();

            try
            {
                if (DbHelper.ExecuteNonQuery(tran, CommandType.StoredProcedure, "bpms_update_business_type", parms) > 0)
                {//类型更新成功
                    if (DeleteAllExtensionsByBusinessTypeId(businessType.ID, tran))
                    {//原来的扩展信息全部删除成功
                        foreach (var extension in businessTypeExtensions)
                        {//添加新的扩展信息
                            InsertBusinessTypeExtension(extension, tran);
                        }

                        tran.Commit();
                        return true;
                    }
                }
            }
            catch (Exception e)
            {
                log.WriteLog(this.businessTypeClassName, e);
                try
                {
                    tran.Rollback();
                }
                catch (Exception ee)
                {
                    log.WriteLog(this.categoryClassName + "更新业务类型失败后事务回滚失败！", ee);
                }
            }
            finally
            {
                tran.Dispose();
                connection.Close();
            }

            return false;
        }

        public bool DeleteBusinessType(int businessTypeId)
        {
            var sql = string.Format("UPDATE {0} SET [is_delete] = 1 WHERE [business_type_id] = @Id", this.categoryTableName);
            DbParameter[] parms = 
            { 
                DbHelper.MakeInParam("@Id", (DbType)SqlDbType.Int, 4, businessTypeId) 
            };
            var connection = new SqlConnection(ConfigContext.GetDBConnectString);
            SqlTransaction tran = connection.BeginTransaction();

            try
            {
                if (DbHelper.ExecuteNonQuery(tran, CommandType.Text, sql, parms) > 0)
                {
                    if (DeleteAllExtensionsByBusinessTypeId(businessTypeId, tran))
                    {
                        tran.Commit();
                        return true;
                    }
                }
            }
            catch (Exception e)
            {
                log.WriteLog(this.businessTypeClassName, e);
                try
                {
                    tran.Rollback();
                }
                catch (Exception ee)
                {
                    log.WriteLog(this.businessTypeClassName + "删除业务类型失败后事务回滚失败", ee);
                }
            }
            finally
            {
                tran.Dispose();
                connection.Close();
            }

            return false;
        }

        public bool BusinessTypeExists(BusinessTypeInfo businessType, OperateType type)
        {
            var sql = string.Format("SELECT 1 FROM {0} WHERE [is_delete] = 0 AND ", this.businessTypeTableName);
            DbParameter[] parms = 
            { 
                DbHelper.MakeInParam("@id", (DbType)SqlDbType.Int, 4, businessType.ID), 
                DbHelper.MakeInParam("@chName", (DbType)SqlDbType.NVarChar, 100, businessType.CHName), 
            };

            switch (type)
            {
                case OperateType.Insert:
                    sql += "[ch_name] = @chName";
                    break;
                case OperateType.Update:
                    sql += "[ch_name] = @chName AND [business_type_id] <> @id";
                    break;
                default:
                    log.WriteLog(this.businessTypeClassName + ":检测业务类型是否存在，参数错误，无指定的检测类型，将按照已经存在处理，防止添加新类型");
                    sql += "1 = 1";
                    break;
            }
            try
            {
                if (DbHelper.ExecuteScalar(CommandType.Text, sql, parms) != null)
                {
                    return true;
                }
            }
            catch (Exception e)
            {
                log.WriteLog(this.businessTypeClassName, e);
            }

            return false;
        }

        public BusinessTypeInfo GetBusinessType(int businessTypeId)
        {
            var sql = string.Format("SELECT * FROM {0} WHERE [business_type_id] = {1}  AND [is_delete] = 0", this.businessTypeTableName, businessTypeId);
            return GetBusinessTypeBySQL(sql);
        }

        public IList<BusinessTypeInfo> GetAllBusinessTypes()
        {
            var sql = string.Format("SELECT * FROM {0} WHERE [is_delete] = 0 ORDER BY [sort_number] ASC", this.businessTypeTableName);
            return GetBusinessTypeListBySQL(sql);
        }

        public IList<BusinessTypeInfo> GetBusinessTypes(int businessCategoryId)
        {
            var sql = string.Format("SELECT * FROM {0} WHERE [business_category_id] = {1}  AND [is_delete] = 0 ORDER BY [sort_number] ASC", this.businessTypeTableName, businessCategoryId);
            return GetBusinessTypeListBySQL(sql);
        }

        public bool IsBusinessTypeSortable(byte sortNumber)
        {
            var sql = string.Format("SELECT 1 FROM {0} WHERE MAX([sort_number]) < @sortNumber OR MIN([sort_number]) > @sortNumber  AND [is_delete] = 0", this.businessTypeTableName);
            DbParameter[] parms = 
            { 
                DbHelper.MakeInParam("@sortNumber", (DbType)SqlDbType.TinyInt, 1, sortNumber) 
            };

            try
            {
                if (DbHelper.ExecuteScalar(CommandType.Text, sql, parms) == null)
                {
                    return true;
                }
            }
            catch (Exception e)
            {
                log.WriteLog(this.businessTypeClassName + "检测业务类型是否可以排序时出现异常，返回不能排序标识，防止排序", e);
            }

            return false;
        }

        public bool SetTypeSortNumber(int businessTypeId, int sortNumber)
        {
            DbParameter[] parms = 
            { 
                DbHelper.MakeInParam("@id", (DbType)SqlDbType.Int, 4, businessTypeId), 
                DbHelper.MakeInParam("@new_sort_number", (DbType)SqlDbType.Int, 4, sortNumber) 
            };

            try
            {
                var result = DbHelper.ExecuteScalar(CommandType.StoredProcedure, "bpms_set_business_type_sort_number", parms);
                return Convert.ToBoolean(result);
            }
            catch (Exception e)
            {
                log.WriteLog(this.businessTypeClassName, e);
                return false;
            }
        }

        public BusinessTypeExtensionInfo GetBusinessTypeExtension(int businessTypeExtensionId)
        {
            var sql = string.Format("SELECT * FROM [{0}business_type_extension] WHERE [business_type_extension_id] = @business_type_extension_id", ConfigContext.GetTablePrefix);
            DbParameter[] parms = 
            { 
                DbHelper.MakeInParam("@business_type_extension_id", (DbType)SqlDbType.Int, 4, businessTypeExtensionId) 
            };

            return GetBusinessTypeExtensionBySQL(sql, parms);
        }

        public IList<BusinessTypeExtensionInfo> GetAllBusinessTypeExtensions()
        {
            var sql = string.Format("SELECT * FROM [{0}business_type_extension]  ORDER BY [sort_number] ASC", ConfigContext.GetTablePrefix);
            return GetBusinessTypeExtensionListBySQL(sql);
        }

        public IList<BusinessTypeExtensionInfo> GetBusinessTypeExtensions(int businessTypeId)
        {
            var sql = string.Format("SELECT * FROM [{0}business_type_extension] WHERE [business_type_id] = @business_type_id  ORDER BY [sort_number] ASC", ConfigContext.GetTablePrefix);
            DbParameter[] parms = 
            { 
                DbHelper.MakeInParam("@business_type_id", (DbType)SqlDbType.Int, 4, businessTypeId) 
            };

            return GetBusinessTypeExtensionListBySQL(sql, parms);
        }

        public MatchRateInfo GetMatchRate(int matchRateId)
        {
            var sql = string.Format("SELECT * FROM [{0}match_rate] WHERE [match_rate_id] = @id", ConfigContext.GetTablePrefix);
            DbParameter[] parms = 
            { 
                DbHelper.MakeInParam("@id", (DbType)SqlDbType.Int, 4, matchRateId) 
            };

            return GetMatchRateBySQL(sql, parms);
        }

        public IList<MatchRateInfo> GetAllMatchRates()
        {
            var sql = string.Format("SELECT * FROM [{0}match_rate]", ConfigContext.GetTablePrefix);

            return GetMatchRateListBySQL(sql);
        }
    }
}
